﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class MeetingRoomDao
    {
        public bool Delete(SqlConnection conn, int roomId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Meeting] ");
            builder.Append("WHERE ");
            builder.Append("\t[RoomId] = @RoomId ");
            builder.Append("DELETE FROM [dbo].[Meeting_Room] ");
            builder.Append("WHERE ");
            builder.Append("\t[RoomId] = @RoomId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RoomId", SqlDbType.Int) };
            commandParameters[0].Value = roomId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, MeetingRoomData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Meeting_Room] ");
            builder.Append("( ");
            builder.Append("\t[Name], ");
            builder.Append("\t[Capacity], ");
            builder.Append("\t[Device], ");
            builder.Append("\t[Description], ");
            builder.Append("\t[Place]\t ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@Name, ");
            builder.Append("\t@Capacity, ");
            builder.Append("\t@Device, ");
            builder.Append("\t@Description, ");
            builder.Append("\t@Place\t ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
            commandParameters[0].Value = data.Name;
            commandParameters[1] = new SqlParameter("@Capacity", SqlDbType.NVarChar, 50);
            if (data.CapacityIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.Capacity;
            }
            commandParameters[2] = new SqlParameter("@Device", SqlDbType.NText);
            if (data.DeviceIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.Device;
            }
            commandParameters[3] = new SqlParameter("@Description", SqlDbType.NText);
            if (data.DescriptionIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Description;
            }
            commandParameters[4] = new SqlParameter("@Place", SqlDbType.NVarChar, 50);
            if (data.PlaceIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Place;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[RoomId], ");
            builder.Append("\t[Name], ");
            builder.Append("\t[Capacity], ");
            builder.Append("\t[Device], ");
            builder.Append("\t[Description], ");
            builder.Append("\t[Place]\t ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting_Room]  ");
            builder.Append("ORDER BY ");
            builder.Append("\t[RoomId] ASC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Meeting_Room";
            return set.Tables[0];
        }

        public DataTable SelectByRoomId(SqlConnection conn, int roomId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Meeting_Room].[RoomId], ");
            builder.Append("\t[Meeting_Room].[Name], ");
            builder.Append("\t[Meeting_Room].[Capacity], ");
            builder.Append("\t[Meeting_Room].[Device], ");
            builder.Append("\t[Meeting_Room].[Description], ");
            builder.Append("\t[Meeting_Room].[Place],\t ");
            builder.Append("\t[Users].[UserName] AS [OperatorName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting_Room] ");
            builder.Append("LEFT OUTER JOIN ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("ON ");
            builder.Append("\t[Meeting_Room].[Operator] = [Users].[UserId] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting_Room].[RoomId] = @RoomId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RoomId", SqlDbType.Int) };
            commandParameters[0].Value = roomId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Meeting_Room";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, MeetingRoomData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Meeting_Room] ");
            builder.Append("SET ");
            builder.Append("\t[Name] = @Name, ");
            builder.Append("\t[Capacity] = @Capacity, ");
            builder.Append("\t[Device] = @Device, ");
            builder.Append("\t[Description] = @Description, ");
            builder.Append("\t[Place] = @Place\t ");
            builder.Append("WHERE ");
            builder.Append("\t[RoomId] = @RoomId ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@RoomId", SqlDbType.Int);
            commandParameters[0].Value = data.RoomId;
            commandParameters[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.Name;
            commandParameters[2] = new SqlParameter("@Capacity", SqlDbType.NVarChar, 50);
            if (data.CapacityIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.Capacity;
            }
            commandParameters[3] = new SqlParameter("@Device", SqlDbType.NText);
            if (data.DeviceIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Device;
            }
            commandParameters[4] = new SqlParameter("@Description", SqlDbType.NText);
            if (data.DescriptionIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Description;
            }
            commandParameters[5] = new SqlParameter("@Place", SqlDbType.NVarChar, 50);
            if (data.PlaceIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Place;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

